﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using ThanhTu.GNCMedia.KQXS.Common;
using ThanhTu.GNCMedia.KQXS.DataAccess.DO;

namespace ThanhTu.GNCMedia.KQXS.DataAccess.DAL
{
    public class UsersDAL : BaseDAL
    {
        public UsersDAL() : base() { }

        public int Add(UsersDO vUsersDO)
        {

            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "Users_PKG.SaveUser";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_UserName", OracleDbType.Varchar2);
            oraParam.Value = vUsersDO.Username;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_Password", OracleDbType.Varchar2);
            oraParam.Value = vUsersDO.Pass;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_FullName", OracleDbType.Varchar2);
            oraParam.Value = vUsersDO.Fullname;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            int result = ExecuteNoneQuery(oraComm);

            if (!Convert.IsDBNull(oraComm.Parameters["outreturn"]))
            { result = Convert.ToInt32(oraComm.Parameters["outreturn"].Value.ToString()); }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return result;
        }

        public int login(string username, string password)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "Users_PKG.CheckLogin";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("IN_UserName", OracleDbType.Varchar2);
            oraParam.Value = username;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_Password", OracleDbType.Varchar2);
            oraParam.Value = password;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("outreturn", OracleDbType.Int32);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            int result = ExecuteNoneQuery(oraComm);

            if (!Convert.IsDBNull(oraComm.Parameters["outreturn"]))
            { result = Convert.ToInt32(oraComm.Parameters["outreturn"].Value.ToString()); }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return result;


        }

        public UsersDO GetByUser(string username, string password)
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "Users_PKG.GetByUser";
            OracleParameter oraParam = null;


            oraParam = new OracleParameter("IN_UserName", OracleDbType.Varchar2);
            oraParam.Value = username;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("IN_Password", OracleDbType.Varchar2);
            oraParam.Value = password;
            oraComm.Parameters.Add(oraParam);

            oraParam = new OracleParameter("outreturn", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            DataSet ds = GetDataSet(oraComm);
            DataRow dr = null;
            UsersDO vUsersDO = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        dr = ds.Tables[0].Rows[0];
                        vUsersDO = new UsersDO();

                        if (!Convert.IsDBNull(dr["USERNAME"]))
                        { vUsersDO.Username = Convert.ToString(dr["USERNAME"]); }

                        if (!Convert.IsDBNull(dr["PASS"]))
                        { vUsersDO.Pass = Convert.ToString(dr["PASS"]); }

                        if (!Convert.IsDBNull(dr["FULLNAME"]))
                        { vUsersDO.Fullname = Utils.ToUTF8String((byte[])dr["FULLNAME"]); }

                        dr = null;
                    }
                }

                ds.Dispose();
                ds = null;
            }

            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return vUsersDO;

        }

        public List<UsersDO> SelectAllUsers()
        {
            OracleCommand oraComm = new OracleCommand();
            oraComm.CommandType = CommandType.StoredProcedure;
            oraComm.CommandText = "Users_PKG.GetAllUser";
            OracleParameter oraParam = null;

            oraParam = new OracleParameter("outreturn", OracleDbType.RefCursor);
            oraParam.Direction = ParameterDirection.Output;
            oraComm.Parameters.Add(oraParam);

            DataSet ds = GetDataSet(oraComm);
            DataTable dt = null;
            List<UsersDO> vUsersDOCollection = null;
            DataRow dr = null;
            UsersDO vUsersDO = null;

            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                    vUsersDOCollection = new List<UsersDO>();

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        dr = dt.Rows[i];
                        vUsersDO = new UsersDO();

                        if (!Convert.IsDBNull(dr["USERNAME"]))
                        { vUsersDO.Username = Convert.ToString(dr["USERNAME"]); }

                        if (!Convert.IsDBNull(dr["PASS"]))
                        { vUsersDO.Pass = Convert.ToString(dr["PASS"]); }

                        if (!Convert.IsDBNull(dr["FULLNAME"]))
                        { vUsersDO.Fullname = Utils.ToUTF8String((byte[])dr["FULLNAME"]); }

                        vUsersDOCollection.Add(vUsersDO);
                        dr = null;
                        vUsersDO = null;
                    }

                    dt.Dispose();
                    dt = null;
                }

                ds.Dispose();
                ds = null;
            }
            oraComm.Dispose();
            oraComm = null;
            oraParam = null;

            return vUsersDOCollection;
        }

    }
}
